capture log close

*Create skill measures from skill-level data set
foreach i in 11 13 15 17 19 21 23 25 27 29 31 33 35 37 39 41 43 45 47 49 51 53 55 99{
	display "`i'"

	use "$root/data/data_restricted/skills07_16.dta", clear
	merge m:1 bgtjobid using "$root/data/data_restricted/data07_16main.dta", keepusing(occfam jobdaten)
	drop if _merge==1
	gen skills=_merge==3
	drop _merge
	replace occfam=99 if occfam==.
	keep if occfam==`i'

	rename skill bg_skill 

	merge m:1 bg_skill using "$root/data/data_restricted/tools_matches.dta"
	gen count_tool=(_merge==3)
	rename CommodityCode tool_cd
	rename CommodityTitle tool_ct
	drop if _merge==2
	drop _merge

	merge m:1 bg_skill using "$root/data/data_restricted/technology_matches.dta"

	gen count_tech=(_merge==3)
	rename CommodityCode tech_cd
	rename CommodityTitle tech_ct
	drop if _merge==2
	drop _merge

	merge m:1 bg_skill using "$root/data/data_restricted/whitecollar_not_tech.dta"
	drop if _merge==2
	drop _merge

	rename bg_skill skill
	merge m:1 skill using "$root/data/data_restricted/top_skills_by_occ.dta"
	drop if _merge==2
	drop _merge
	merge m:1 skill using "$root/data/data_restricted/top_skills_by_occ_cat.dta"
	drop if _merge==2
	drop _merge
	foreach var in management_top100 business_top100 legal_top100 sales_top100 wc_male_top100 wc_female_top100 pc_top100 bc_top100{
		replace `var'=0 if `var'==.
		replace `var'=0 if count_tech==1|skill=="JAVA"|skill=="LINUX"|skill=="PERL"
	}

	rename skill bg_skill
	gen cognitive= bg_skill=="Problem Solving" | bg_skill=="Research"|strpos(bg_skill,"Analytical")>0|bg_skill=="Critical Thinking"|strpos(bg_skill,"Math")>0|bg_skill=="Statistics"
	gen social= bg_skill=="Communication Skills"|bg_skill=="Team Work"|bg_skill=="Collaboration"|bg_skill=="Negotiation Skills"|bg_skill=="Presentation Skills"
	gen character=strpos(bg_skill, "Organizat")>0|bg_skill=="Detail-Oriented"|bg_skill=="Multi-Tasking"|bg_skill=="Time Management"|bg_skill=="Meeting Deadlines"|bg_skill=="Energetic"
	gen financial=bg_skill=="Budgeting"|bg_skill=="Accounting"|bg_skill=="Finance"|strpos(bg_skill, "Cost")>0
	gen customerservice=strpos(bg_skill, "Customer")>0|bg_skill=="Sales"|strpos(bg_skill, "Client")>0|strpos(bg_skill, "Patient")>0
	gen writing=bg_skill=="Writing"
	gen projectmanage=bg_skill=="Project Management"
	gen peoplemanage=(bg_skill=="Supervisory Skills"|bg_skill=="Leadership"|bg_skill=="Management"|bg_skill=="Mentoring"| strpos(bg_skill, "Staff")>0)&projectmanage==0
	gen m_excel =  bg_skill== "Microsoft Excel"
	gen m_office =  bg_skill== "Microsoft Office"
	gen spreadsheet =  strpos(tech_ct, "Spreadsheet")>0
	gen office=  strpos(tech_ct, "Office suite")>0	
	gen word =  strpos(tech_ct, "Word process")>0
	*collapse to ad-level data set
	collapse (sum) count_tech skills basic_aa clerk mail routine_accounting light_physical legal accounting_finance sales_cust marketing logistics hr research higher_level_mgmt other_cognitive writing bc_top100 pc_top100 wc_male_top100 wc_female_top100  management_top100 business_top100 legal_top100 sales_top100 cognitive social character  projectmanage peoplemanage financial customerservice issoftware spreadsheet office word m_excel m_office  , by (bgtjobid)

	*Merge to ad-level data set
	merge 1:1 bgtjobid using "$root/data/data_restricted/data07_16main.dta"
	drop if _merge==2
	drop _merge

	gen year=year(jobdaten)
	drop if fips==.
	*FIPS code changes: http://www.ddorn.net/data/FIPS_County_Code_Changes.pdf
	replace fips =12025 if fips==12086
	rename fips cty_fips
	merge m:1 cty_fips using "$root/data/data_public/cw_cty_czone.dta"
	rename cty_fips fips
	replace fips=12086 if fips==12025
	replace czone= 28900 if fips==8014

	keep if _merge==3
	drop _merge

	replace skills=0 if skills==.
	gen listskills=(skills>0)
	gen any_tech= count_tech!=.& count_tech!=0
	gen tech= count_tech!=.& count_tech!=0
	replace m_excel =1 if spreadsheet==1
	replace m_office=1 if word==1|m_excel==1|(office>0&office!=.)
	save "$root/data/interim/temp2.dta", replace

	*Identify technology adoption at the firm--two-digit-soc--czone
	do "$root/code/build/identify_adoption_czone.do"

	*Create other ad-level variables
	use "$root/data/interim/temp2.dta", replace

	*Experience--interpret no experience listed as zero years
	gen exper= 0
	replace exper = exp if exp!=.

	*Indicator for listing experience
	gen listsexper=0
	replace listsexper=1 if exp!=.

	*Years of education
	gen educ_cond=edu
	gen educ=edu
	replace educ=0 if edu==.

	*Indicators for listing education
	gen listseduc=0
	replace listseduc=1 if edu!=.

	egen emp_id=group(employer)
	gen any_issoftware=issoftware>0&issoftware!=.
	merge m:1 year czone employer using "$root/data/interim/firmocc_lists_tech.dta"
	drop if _merge==2
	drop _merge

	foreach var in liyo_tech {
		replace `var'= 0 if `var'==.
	}

	foreach var in tech {

		egen elifo_`var'=max(liyo_`var'), by (czone employer)
		gen yofl_`var'b=year if liyo_`var'==1
		egen yofl_`var'=min(yofl_`var'b), by (czone employer)
		gen fol_`var'=(year>=yofl_`var')
		drop yofl_`var'b 
	}
	egen narrow_employer=group(czone employer)
	gen yprehireb=year<2011
	egen yprehire=max(yprehireb), by (narrow_employer)

	foreach var in  tech {
		gen ypre`var'b =year<2011&`var'==1
		egen ypre`var'=max(ypre`var'b), by (narrow_employer) 
		drop ypre`var'b
	}

	egen soc_employer=group(soc emp_id)
	egen soc_id=group(soc)

	keep listseduc listsexper educ exper any_tech yofl_tech czone soc year emp_id narrow_employer cleantitle ypretech count_tech skills basic_aa clerk mail routine_accounting light_physical legal accounting_finance sales_cust marketing logistics hr research higher_level_mgmt other_cognitive writing bc_top100 pc_top100 wc_male_top100 wc_female_top100 soc cognitive social character writing projectmanage peoplemanage financial customerservice employer jobdaten educ_cond management_top100 business_top100 legal_top100 sales_top100 issoftware any_issoftware office m_excel m_office

	compress
	if `i'!=11{
		append using "$root/data/interim/adminassistants_bg.dta"
	}

	save "$root/data/interim/adminassistants_bg.dta", replace

}
